﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Diagnostics;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;

public partial class UC_BCDTGiaVL : System.Web.UI.UserControl
{
    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    NpoiExport cl = new NpoiExport();
    string frmName = "frmBaoCaoDoanhThu";
    static int f_SuDung = 0, f_XemTatCa = 0, f_InBaoCao = 0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static int PageNumber = 10;
    static int CurrentPage = 1;
    static double kltx = 0, klban = 0, ttban = 0, ttvl = 0, dthu = 0;
    static DataTable dt;
    static DataTable dtPrint;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Convert.ToString(Session["user"])) || string.IsNullOrEmpty(Convert.ToString(Session["IDUser"])))
            Response.Redirect("Default.aspx");
        if (!IsPostBack)
        {
            string idnguoidung = Session["IDUser"].ToString();
            string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

            System.Data.DataTable dt2 = ex.GetData_Text(sql);
            if (dt2.Rows.Count > 0)
            {
                f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
            }


            CurrentPage = 1;
            for (int i = 1; i <= 12; i++)
            {
                dlThang.Items.Add(new ListItem("Tháng " + i.ToString(), i.ToString()));
            }
            dlThang.Items.Insert(0, new ListItem("Theo thời gian", ""));
            dlThang.SelectedIndex = 0;

            int year1 = DateTime.Now.Year - 3;
            int year2 = DateTime.Now.Year;
            for (int i = year2; i >= year1; i--)
            {
                dlNam.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));
            }
            dlNam.SelectedValue = year2.ToString();

            txtTuNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            txtDenNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            dlNam.Visible = false;


            LoadKhachHang(dlKhachHang);
            LoadLoaiKH(dlLoaiKH);
            //LoadTramTron(dlTramTron);
            LoadCongTrinh(dlCongTrinh);
            LoadMacHD(dlMacHD);
            LoadLoaiDa(dlLoaiDa);
        }
    }
    void LoadTramTron(DropDownList dlTramTron)
    {
        dlTramTron.Items.Clear();
        dlTramTron.DataSource = ex.GetData("sp_xdanhaphang_ListTramTron");
        dlTramTron.DataBind();
        dlTramTron.Items.Insert(0, new ListItem("--Chọn trạm trộn--", ""));
        dlTramTron.SelectedIndex = 0;
    }
    void LoadLoaiKH(DropDownList dlLoaiKH)
    {
        dlLoaiKH.Items.Clear();
        dlLoaiKH.DataSource = ex.GetData("sp_xdaloaikhachhang_ListDrop");
        dlLoaiKH.DataBind();
        dlLoaiKH.Items.Insert(0, new ListItem("--Chọn LKH--", ""));
        dlLoaiKH.SelectedIndex = 0;
    }
    void LoadKhachHang(DropDownList dl)
    {
        dl.Items.Clear();
        dl.Items.Insert(0, new ListItem("--Chọn khách hàng--", ""));
        dl.SelectedIndex = 0;
    }
    protected void dlLoaiBaoCao_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlLoaiBaoCao.SelectedIndex.Equals(0))
            Response.Redirect("BCDTTongHop.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(1))
            Response.Redirect("BCDTMacHopDong.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(2))
            Response.Redirect("BCDTBienSoXe.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(3))
            Response.Redirect("BCDTNVKD.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(4))
            Response.Redirect("BCDTXeBom.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(5))
            Response.Redirect("BCDTGiaVatLieu.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(6))
            Response.Redirect("BCDTKhachHang.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(7))
            Response.Redirect("BCDTVatLieuTieuHao.aspx");
    }
    protected void btnXem_Click(object sender, EventArgs e)
    {
        if (f_SuDung.Equals(1))
        {
            CurrentPage = 1;
            btnNext.Enabled = true;
            Search(CurrentPage);
        }
        else
            gstGetMess("Bạn không có quyền xem báo cáo này", "");
    }
    void Search(int page)
    {
        //
        int index = 3;
        if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
            index = 1;
        else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex.Equals(0) && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
            index = 2;
        else if (dlLoaiKH.SelectedIndex.Equals(0) && dlKhachHang.SelectedIndex.Equals(0) && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
            index = 3;
        else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
            index = 4;
        else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex > 0 && dlLoaiDa.SelectedIndex.Equals(0))
            index = 5;
        else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex > 0 && dlLoaiDa.SelectedIndex > 0)
            index = 6;

        getDate();
        p = new SqlParameter[10];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@LoaiKH", ex.GetGuid(dlLoaiKH.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
        p[3].SqlDbType = SqlDbType.UniqueIdentifier;
        p[4] = new SqlParameter("@Index", index);
        p[4].SqlDbType = SqlDbType.Int;
        p[5] = new SqlParameter("@PageNumber", page);
        p[5].SqlDbType = SqlDbType.Int;
        p[6] = new SqlParameter("@RowspPage", PageNumber);
        p[6].SqlDbType = SqlDbType.Int;
        p[7] = new SqlParameter("@CongTrinh", ex.GetGuid(dlCongTrinh.SelectedValue));
        p[7].SqlDbType = SqlDbType.UniqueIdentifier;
        p[8] = new SqlParameter("@MacHD", ex.GetGuid(dlMacHD.SelectedValue));
        p[8].SqlDbType = SqlDbType.UniqueIdentifier;
        p[9] = new SqlParameter("@LoaiDa", int.Parse(dlLoaiDa.SelectedValue));
        p[9].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_BCDT_GiaVatLieu", p);
        GV.DataBind();

        //get footer
        p = new SqlParameter[8];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@LoaiKH", ex.GetGuid(dlLoaiKH.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
        p[3].SqlDbType = SqlDbType.UniqueIdentifier;
        p[4] = new SqlParameter("@Index", index);
        p[4].SqlDbType = SqlDbType.Int;
        p[5] = new SqlParameter("@CongTrinh", ex.GetGuid(dlCongTrinh.SelectedValue));
        p[5].SqlDbType = SqlDbType.UniqueIdentifier;
        p[6] = new SqlParameter("@MacHD", ex.GetGuid(dlMacHD.SelectedValue));
        p[6].SqlDbType = SqlDbType.UniqueIdentifier;
        p[7] = new SqlParameter("@LoaiDa", int.Parse(dlLoaiDa.SelectedValue));
        p[7].SqlDbType = SqlDbType.Int;

        if (GV.Rows.Count > 0)
        {
            dt = ex.GetData("sp_BCDT_GiaVatLieu_GetFooter", p);
            if (dt.Rows.Count > 0)
            {
                kltx = double.Parse(dt.Rows[0]["KLThucXuat"].ToString());
                klban = double.Parse(dt.Rows[0]["KLKhachHang"].ToString());
                ttvl = double.Parse(dt.Rows[0]["TTVL"].ToString());
                ttban = double.Parse(dt.Rows[0]["TTGiaHD"].ToString());
                dthu = double.Parse(dt.Rows[0]["DTGiaVL"].ToString());

                GV.FooterRow.Cells[2].Text = "Tổng cộng";
                GV.FooterRow.Cells[2].HorizontalAlign = HorizontalAlign.Center;
                GV.FooterRow.Cells[5].Text = ex.ConvertDecimal(kltx);
                GV.FooterRow.Cells[7].Text = ex.ConvertDecimal(klban);
                GV.FooterRow.Cells[10].Text = ex.ConvertDecimal(ttvl);

                GV.FooterRow.Cells[12].Text = ex.ConvertDecimal(ttban);
                GV.FooterRow.Cells[13].Text = ex.ConvertDecimal(dthu);
            }
        }
    }
    void PrintNPOI(System.Data.DataTable dt)
    {
        var workbook = new HSSFWorkbook();

        if (dt.Rows.Count > 0)
        {
            string sheetname = "Bảng tổng hợp";

            if (dlKhachHang.SelectedIndex > 0)
                sheetname = dlKhachHang.SelectedItem.Text;
            else if (dlLoaiKH.SelectedIndex > 0)
                sheetname = "Bảng tổng hợp-" + dlLoaiKH.SelectedItem.Text;
            else sheetname = "Bảng tổng hợp";

            var sheet = workbook.CreateSheet(sheetname);

            #region CSS
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.Alignment = HorizontalAlignment.LEFT;
            headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
            headerLabelCellStyle.BorderLeft = CellBorderType.THIN;
            headerLabelCellStyle.BorderRight = CellBorderType.THIN;
            headerLabelCellStyle.BorderTop = CellBorderType.THIN;

            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.FontName = "Times New Roman";

            headerLabelFont.FontHeightInPoints = 11;
            headerLabelFont.Color = HSSFColor.BLACK.index;
            headerLabelCellStyle.SetFont(headerLabelFont);

            //--------------------------

            var hsRight = workbook.CreateCellStyle();
            hsRight.Alignment = HorizontalAlignment.RIGHT;
            hsRight.BorderBottom = CellBorderType.THIN;
            hsRight.BorderLeft = CellBorderType.THIN;
            hsRight.BorderRight = CellBorderType.THIN;
            hsRight.BorderTop = CellBorderType.THIN;

            hsRight.SetFont(headerLabelFont);
            //-------------------
            var hsCenter = workbook.CreateCellStyle();
            hsCenter.Alignment = HorizontalAlignment.CENTER;
            hsCenter.BorderBottom = CellBorderType.THIN;
            hsCenter.BorderLeft = CellBorderType.THIN;
            hsCenter.BorderRight = CellBorderType.THIN;
            hsCenter.BorderTop = CellBorderType.THIN;

            hsCenter.SetFont(headerLabelFont);
            //-----------------------

            var hs1 = workbook.CreateCellStyle();
            hs1.Alignment = HorizontalAlignment.LEFT;

            var hsb = workbook.CreateFont();
            hsb.Boldweight = (short)FontBoldWeight.BOLD;
            hsb.FontName = "Times New Roman";

            hsb.FontHeightInPoints = 11;
            hsb.Color = HSSFColor.BLACK.index;
            hs1.SetFont(hsb);

            //-------------
            var hs2 = workbook.CreateCellStyle();
            hs2.Alignment = HorizontalAlignment.CENTER;

            var hsb2 = workbook.CreateFont();
            hsb2.Boldweight = (short)FontBoldWeight.BOLD;
            hsb2.FontName = "Times New Roman";

            hsb2.FontHeightInPoints = 18;
            hsb2.Color = HSSFColor.BLACK.index;
            hs2.SetFont(hsb2);
            //------------------------------
            var hs3 = workbook.CreateCellStyle();
            hs3.Alignment = HorizontalAlignment.CENTER;
            hs3.BorderBottom = CellBorderType.THIN;
            var hsb3 = workbook.CreateFont();
            hsb3.Boldweight = (short)FontBoldWeight.BOLD;
            hsb3.FontName = "Times New Roman";

            hsb3.FontHeightInPoints = 14;
            hsb3.Color = HSSFColor.BLACK.index;
            hs3.SetFont(hsb3);

            //----------------------------
            var hs4 = workbook.CreateCellStyle();
            hs4.Alignment = HorizontalAlignment.CENTER;

            hs4.BorderBottom = CellBorderType.THIN;
            hs4.BorderLeft = CellBorderType.THIN;
            hs4.BorderRight = CellBorderType.THIN;
            hs4.BorderTop = CellBorderType.THIN;

            var hsb4 = workbook.CreateFont();
            hsb4.Boldweight = (short)FontBoldWeight.BOLD;
            hsb4.FontName = "Times New Roman";

            hsb4.FontHeightInPoints = 11;
            hsb4.Color = HSSFColor.BLACK.index;
            hs4.SetFont(hsb4);

            #endregion

            //Start header-----------------------------------------------------------------------
            #region Header
            //tao hnag dau tien
            var rowIndex = 0;

            //cong ty
            var row = sheet.CreateRow(rowIndex);
            Cell r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            string ngaythang = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text.ToUpper() + " " + dlNam.SelectedItem.Text.ToUpper() : "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("TỔNG HỢP DOANH THU THEO GIÁ VẬT LIỆU " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(2, 2, 0, 12);
            sheet.AddMergedRegion(cra);

            //khachhang
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue(dlKhachHang.SelectedIndex > 0 ? "Khách hàng: " + dlKhachHang.SelectedItem.Text : dlLoaiKH.SelectedIndex > 0 ? "Loại khách hàng: " + dlLoaiKH.SelectedItem.Text : "");
            r1c1.CellStyle = hs3;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(3, 3, 0, 12);
            sheet.AddMergedRegion(cra);

            //freeze panes
            sheet.CreateFreezePane(0, 6);
            #endregion
            //End header-----------------------------------------------------------------------

            #region Header1-2

            //header2
            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            string[] header = { "Ngày xuất", "Khách hàng", "Độ sụt", "Mác thực xuất", "KL thực xuất", "Mác HĐ", "KL bán", "Loại đá", "Giá vật liệu", "TT vật liệu", "Giá HĐ", "TT bê tông", "Doanh thu giá VL" };

            var cell = row.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;


            //tao tieu de cot
            for (int j = 0; j < header.Length; j++)
            {
                cell = row.CreateCell(j + 1);
                cell.SetCellValue(header[j].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;
            }
            #endregion

            //ghi du lieu tung dong
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                cell = row.CreateCell(0);
                cell.SetCellValue((i + 1).ToString());
                cell.CellStyle = hsCenter;

                for (int j = 1; j <= dt.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);

                    if (j == 5 || j == 7 || j >= 9)
                    {
                        if (!dt.Rows[i][j - 1].ToString().Trim().Equals(""))
                        {
                            cell.SetCellType(CellType.NUMERIC);
                            cell.SetCellValue(double.Parse(dt.Rows[i][j - 1].ToString()));
                        }
                        else
                            cell.SetCellValue(dt.Rows[i][j - 1].ToString());
                        cell.CellStyle = hsRight;
                    }
                    else
                    {
                        cell.SetCellValue(dt.Rows[i][j - 1].ToString());

                        if (j <= 1)
                            cell.CellStyle = hsCenter;
                        else
                            cell.CellStyle = headerLabelCellStyle;
                    }
                    cell.Row.Height = 500;
                }

            }

            #region Footer

            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(2);
            cell.SetCellValue("Tổng cộng");
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            string[] footer = { "F", "G", "H", "I", "J", "K", "L", "M", "N" };
            for (int k = 5; k <= 13; k++)
            {
                if (k != 6 && k != 8 && k != 9 && k != 11)
                {
                    cell = row.CreateCell(k);
                    cell.CellFormula = "SUM(" + footer[k - 5] + "7:" + footer[k - 5] + rowIndex.ToString() + ")";
                    cell.Row.Height = 500;
                    cell.CellStyle = hs4;
                }
            }
            //fix lai border


            for (int ik = 0; ik <= 13; ik++)
            {
                if (ik != 2 && ik <= 4 || ik == 6 || ik == 8 || ik == 9 || ik == 11)
                {
                    r1c1 = row.CreateCell(ik);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }
                sheet.AutoSizeColumn(ik);
            }
            #endregion

            //end sheet tong hop--------------------------

            int index = 3;
            if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                index = 1;
            else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex.Equals(0) && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                index = 2;
            else if (dlLoaiKH.SelectedIndex.Equals(0) && dlKhachHang.SelectedIndex.Equals(0) && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                index = 3;
            else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                index = 4;
            else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex > 0 && dlLoaiDa.SelectedIndex.Equals(0))
                index = 5;
            else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex > 0 && dlLoaiDa.SelectedIndex > 0)
                index = 6;

            p = new SqlParameter[8];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@LoaiKH", ex.GetGuid(dlLoaiKH.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            p[3] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
            p[3].SqlDbType = SqlDbType.UniqueIdentifier;
            p[4] = new SqlParameter("@Index", index);
            p[4].SqlDbType = SqlDbType.Int;
            p[5] = new SqlParameter("@CongTrinh", ex.GetGuid(dlCongTrinh.SelectedValue));
            p[5].SqlDbType = SqlDbType.UniqueIdentifier;
            p[6] = new SqlParameter("@MacHD", ex.GetGuid(dlMacHD.SelectedValue));
            p[6].SqlDbType = SqlDbType.UniqueIdentifier;
            p[7] = new SqlParameter("@LoaiDa", int.Parse(dlLoaiDa.SelectedValue));
            p[7].SqlDbType = SqlDbType.Int;

            dt = ex.GetData("sp_BCDT_GiaVatLieu_ThanhTienDoanhThu", p);
            if (dt.Rows.Count > 0)
            {
                sheet = workbook.CreateSheet("Thành tiền doanh thu");
                #region Header
                //tao hnag dau tien
                rowIndex = 0;

                //cong ty
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                r1c1.CellStyle = hs1;
                r1c1.Row.Height = 400;

                cra = new CellRangeAddress(0, 0, 0, 4);
                sheet.AddMergedRegion(cra);

                //chi nhanh
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                r1c1.CellStyle = hs1;
                r1c1.Row.Height = 400;

                cra = new CellRangeAddress(1, 1, 0, 4);
                sheet.AddMergedRegion(cra);

                //tieu de bao cao
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("TỔNG HỢP DOANH THU THEO GIÁ VẬT LIỆU " + ngaythang);
                r1c1.CellStyle = hs2;
                r1c1.Row.Height = 500;

                cra = new CellRangeAddress(2, 2, 0, 8);
                sheet.AddMergedRegion(cra);

                //khachhang
                rowIndex++;
                //row = sheet.CreateRow(rowIndex);
                //r1c1 = row.CreateCell(0);
                //r1c1.SetCellValue(dlKhachHang.SelectedIndex > 0 ? "Khách hàng: " + dlKhachHang.SelectedItem.Text : dlLoaiKH.SelectedIndex > 0 ? "Loại khách hàng: " + dlLoaiKH.SelectedItem.Text : "");
                //r1c1.CellStyle = hs3;
                //r1c1.Row.Height = 500;

                //cra = new CellRangeAddress(3, 3, 0, 8);
                //sheet.AddMergedRegion(cra);

                //freeze panes
                sheet.CreateFreezePane(0, 6);
                #endregion
                #region Header1-2

                //header2
                rowIndex++;
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                string[] header5 = { "Khách hàng", "Mác HĐ", "Loại đá", "Khối lượng", "Đơn giá", "Thành tiền bê tông", "Thành tiền giá VL", "Doanh thu giá VL" };

                cell = row.CreateCell(0);
                cell.SetCellValue("STT");
                cell.CellStyle = hs4;
                cell.Row.Height = 400;


                //tao tieu de cot
                for (int j = 0; j < header5.Length; j++)
                {
                    cell = row.CreateCell(j + 1);
                    cell.SetCellValue(header5[j].ToString());
                    cell.CellStyle = hs4;
                    cell.Row.Height = 500;
                }
                #endregion
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                    cell = row.CreateCell(0);
                    cell.SetCellValue((i + 1).ToString());
                    cell.CellStyle = hsCenter;

                    for (int j = 1; j < dt.Columns.Count; j++)
                    {
                        cell = row.CreateCell(j);

                        if (j >= 4)
                        {
                            if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                            {
                                cell.SetCellType(CellType.NUMERIC);
                                cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                            }
                            else
                                cell.SetCellValue(dt.Rows[i][j].ToString());
                            cell.CellStyle = hsRight;
                        }
                        else
                        {
                            cell.SetCellValue(dt.Rows[i][j].ToString());

                            if (j == 2 || j == 3)
                                cell.CellStyle = hsCenter;
                            else
                                cell.CellStyle = headerLabelCellStyle;
                        }
                        cell.Row.Height = 500;
                    }

                }
                #region Footer

                rowIndex++;
                row = sheet.CreateRow(rowIndex);

                cell = row.CreateCell(1);
                cell.SetCellValue("Tổng cộng");
                cell.Row.Height = 500;
                cell.CellStyle = hs4;

                string[] footer5 = { "E", "F", "G", "H", "I" };
                for (int k = 4; k <= 8; k++)
                {
                    if (k != 5)
                    {
                        cell = row.CreateCell(k);
                        cell.CellFormula = "SUM(" + footer5[k - 4] + "7:" + footer5[k - 4] + rowIndex.ToString() + ")";
                        cell.Row.Height = 500;
                        cell.CellStyle = hs4;
                    }
                }
                //fix lai border


                for (int ik = 0; ik <= 8; ik++)
                {
                    if (ik == 0 || ik == 2 || ik == 3 || ik == 5)
                    {
                        r1c1 = row.CreateCell(ik);
                        r1c1.CellStyle = hs4;
                        r1c1.Row.Height = 400;
                    }
                    sheet.AutoSizeColumn(ik);
                }
                #endregion
            }

            using (var exportData = new MemoryStream())
            {
                workbook.Write(exportData);
                string filename = dlKhachHang.SelectedIndex > 0 ? dlKhachHang.SelectedItem.Text : dlLoaiKH.SelectedIndex > 0 ? dlLoaiKH.SelectedItem.Text : "";
                string saveAsFileName = "BCDT-GiaVL-" + filename + " " + ngaythang + ".xls";

                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                Response.Clear();
                Response.BinaryWrite(exportData.GetBuffer());
                Response.End();
            }
        }
    }
    protected void btnPrint_Click(object sender, EventArgs e)
    {
        if (f_InBaoCao.Equals(1))
        {
            try
            {
                int index = 3;
                if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                    index = 1;
                else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex.Equals(0) && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                    index = 2;
                else if (dlLoaiKH.SelectedIndex.Equals(0) && dlKhachHang.SelectedIndex.Equals(0) && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                    index = 3;
                else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                    index = 4;
                else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex > 0 && dlLoaiDa.SelectedIndex.Equals(0))
                    index = 5;
                else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex > 0 && dlLoaiDa.SelectedIndex > 0)
                    index = 6;

                getDate();
                p = new SqlParameter[8];
                p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                p[0].SqlDbType = SqlDbType.SmallDateTime;
                p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                p[1].SqlDbType = SqlDbType.SmallDateTime;
                p[2] = new SqlParameter("@LoaiKH", ex.GetGuid(dlLoaiKH.SelectedValue));
                p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                p[3] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
                p[3].SqlDbType = SqlDbType.UniqueIdentifier;
                p[4] = new SqlParameter("@Index", index);
                p[4].SqlDbType = SqlDbType.Int;
                p[5] = new SqlParameter("@CongTrinh", ex.GetGuid(dlCongTrinh.SelectedValue));
                p[5].SqlDbType = SqlDbType.UniqueIdentifier;
                p[6] = new SqlParameter("@MacHD", ex.GetGuid(dlMacHD.SelectedValue));
                p[6].SqlDbType = SqlDbType.UniqueIdentifier;
                p[7] = new SqlParameter("@LoaiDa", int.Parse(dlLoaiDa.SelectedValue));
                p[7].SqlDbType = SqlDbType.Int;

                dtPrint = ex.GetData("sp_BCDT_GiaVatLieu_Print", p);
                PrintNPOI(dtPrint);
            }
            catch (Exception ax)
            {
                gstGetMess(ax.Message, "");

            }
        }
        else
            gstGetMess("Bạn không có quyền in báo cáo này", "");
    }
    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }
    protected void dlThang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            txtTuNgay.Visible = true;
            txtDenNgay.Visible = true;
            dlNam.Visible = false;
        }
        else
        {
            txtTuNgay.Visible = false;
            txtDenNgay.Visible = false;
            dlNam.Visible = true;
        }
        dlLoaiKH.SelectedIndex = 0;
        LoadKhachHang(dlKhachHang);

        getDate();

    }
    public void getDate()
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
        else
        {
            txtTuNgay.Text = "1/" + dlThang.SelectedValue + "/" + dlNam.SelectedValue;
            txtDenNgay.Text = GetLastDayOfMonth(int.Parse(dlThang.SelectedValue)).ToString("dd/MM/yyyy");
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
    }
    public DateTime GetLastDayOfMonth(int iMonth)
    {
        DateTime dtResult = new DateTime(int.Parse(dlNam.SelectedValue), iMonth, 1);
        dtResult = dtResult.AddMonths(1);
        dtResult = dtResult.AddDays(-(dtResult.Day));
        return dtResult;
    }
    protected void dlLoaiKH_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlLoaiKH.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@LoaiKH", ex.GetGuid(dlLoaiKH.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;

            dlKhachHang.Items.Clear();
            dlKhachHang.DataSource = ex.GetData("sp_BCXH_LoadKhachHangByLoaiKH", p);
            dlKhachHang.DataBind();
            dlKhachHang.Items.Insert(0, new ListItem("--Chọn khách hàng--", ""));
            dlKhachHang.SelectedIndex = 0;
        }
        else
        {
            LoadKhachHang(dlKhachHang);
        }
        LoadCongTrinh(dlCongTrinh);
        LoadMacHD(dlMacHD);
        LoadLoaiDa(dlLoaiDa);
    }
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;

        Search(CurrentPage);

        btnPre.Enabled = false;
        btnNext.Enabled = true;
        btnFirst.Enabled = false;
    }
    protected void btnPre_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;
            btnPre.Enabled = true;
            btnNext.Enabled = true;
            btnFirst.Enabled = true;

            Search(CurrentPage);
        }
        else
        {
            btnFirst.Enabled = false;
            btnPre.Enabled = false;
            btnNext.Enabled = true;
        }
    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        CurrentPage++;

        Search(CurrentPage);

        if (GV.Rows.Count.Equals(0))
        {
            btnPre.Enabled = true;
            btnNext.Enabled = false;
        }
        else
        {
            btnPre.Enabled = true;
            btnNext.Enabled = true;
        }
        btnFirst.Enabled = true;
    }
    protected void GV_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == System.Web.UI.WebControls.DataControlRowType.DataRow)
        {
            e.Row.Attributes.Add("onmouseover", "this.originalstyle=this.style.backgroundColor;this.style.backgroundColor='#EEFFAA'");
            e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=this.originalstyle;");
        }
    }
    protected void dlKhachHang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlKhachHang.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;

            dlCongTrinh.Items.Clear();
            dlCongTrinh.DataSource = ex.GetData("sp_BCXH_LoadCongTrinhByKhachHang", p);
            dlCongTrinh.DataBind();
            dlCongTrinh.Items.Insert(0, new ListItem("--Chọn công trình--", ""));
            dlCongTrinh.SelectedIndex = 0;
        }
        else
        {
            LoadCongTrinh(dlCongTrinh);

        }
        LoadMacHD(dlMacHD);
        LoadLoaiDa(dlLoaiDa);
    }
    protected void dlCongTrinh_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlCongTrinh.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@CongTrinh", ex.GetGuid(dlCongTrinh.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;

            dlMacHD.Items.Clear();
            dlMacHD.DataSource = ex.GetData("sp_BCXH_LoadMacHDByCongTrinh", p);
            dlMacHD.DataBind();
            dlMacHD.Items.Insert(0, new ListItem("--Chọn mác HĐ--", ""));
            dlMacHD.SelectedIndex = 0;
        }
        else
            LoadMacHD(dlMacHD);
        LoadLoaiDa(dlLoaiDa);
    }
    protected void dlMacHD_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlMacHD.SelectedIndex > 0)
        {
            p = new SqlParameter[4];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@CongTrinh", ex.GetGuid(dlCongTrinh.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            p[3] = new SqlParameter("@Mac", ex.GetGuid(dlMacHD.SelectedValue));
            p[3].SqlDbType = SqlDbType.UniqueIdentifier;

            dlLoaiDa.Items.Clear();
            dlLoaiDa.DataSource = ex.GetData("sp_BCXH_LoadLoaiDaByMacCongTrinh", p);
            dlLoaiDa.DataBind();
            dlLoaiDa.Items.Insert(0, new ListItem("--Chọn loại đá--", "0"));
            dlLoaiDa.SelectedIndex = 0;
        }
        else
            LoadLoaiDa(dlLoaiDa);
    }
    void LoadCongTrinh(DropDownList dl)
    {
        dl.Items.Clear();
        dl.Items.Insert(0, new ListItem("--Chọn công trình--", ""));
        dl.SelectedIndex = 0;
    }
    void LoadMacHD(DropDownList dl)
    {
        dl.Items.Clear();
        dl.Items.Insert(0, new ListItem("--Chọn mác HĐ--", ""));
        dl.SelectedIndex = 0;
    }
    void LoadLoaiDa(DropDownList dl)
    {
        dl.Items.Clear();
        dl.Items.Insert(0, new ListItem("--Chọn loại đá--", "0"));
        dl.SelectedIndex = 0;
    }
}